package data;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import logic.Answer;
import logic.Degree;
import logic.Question;
import logic.Test;
import logic.University;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DB_Helper extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "evaluity.database";

	// Database Path
	private static final String DATABASE_PATH = "/data/data/com.evaluity/databases/";

	// Database Tables
	private static enum tables {
		TABLE_TESTS, TABLE_QUESTIONS, TABLE_ANSWERS, TABLE_UNIVERSITIES, TABLE_DEGREES, TABLE_TEST_ACCESS
	};

	/**
	 * 
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public DB_Helper(Context context, CursorFactory factory) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Drop older table if existed
//		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_TESTS);
//		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_QUESTIONS);
//		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_ANSWERS);

		String CREATE_TESTS_TABLE = "CREATE TABLE " + tables.TABLE_TESTS + "("
				+ Test.KEY_ID + " TEXT PRIMARY KEY," + Test.KEY_NAME + " TEXT,"
				+ Test.COURSE_KEY + " TEXT )";
		db.execSQL(CREATE_TESTS_TABLE);

		String CREATE_QUESTIONS_TABLE = "CREATE TABLE "
				+ tables.TABLE_QUESTIONS + "(" + Question.KEY_ID
				+ " TEXT PRIMARY KEY," + Question.KEY_TEST_ID + " TEXT,"
				+ Question.KEY_TEXT + " TEXT," 
				+ Question.KEY_CORRECT_ANSWER + " TEXT )";
		db.execSQL(CREATE_QUESTIONS_TABLE);

		String CREATE_ANSWERS_TABLE = "CREATE TABLE " + tables.TABLE_ANSWERS
				+ "(" + Answer.KEY_ID + " TEXT PRIMARY KEY,"
				+ Answer.KEY_QUESTION_ID + " TEXT," + Answer.KEY_TEXT
				+ " TEXT)";
		db.execSQL(CREATE_ANSWERS_TABLE);

		String CREATE_UNIVERSITIES_TABLE = "CREATE TABLE "
				+ tables.TABLE_UNIVERSITIES + "(" + University.KEY_ID
				+ " TEXT PRIMARY KEY," + University.NAME + " TEXT,"
				+ University.ACRONYM + " TEXT, " + University.REGION
				+ " TEXT )";
		db.execSQL(CREATE_UNIVERSITIES_TABLE);

		String CREATE_DEGREES_TABLE = "CREATE TABLE " + tables.TABLE_DEGREES
				+ "(" + Degree.KEY_ID + " TEXT PRIMARY KEY," + Degree.NAME
				+ " TEXT," + Degree.UNI_KEY + " TEXT )";
		db.execSQL(CREATE_DEGREES_TABLE);
		
		String CREATE_TEST_ACCESS_TABLE = "CREATE TABLE " + tables.TABLE_TEST_ACCESS
				+ "(" + Test.KEY_ID + " TEXT PRIMARY KEY," 
				+ Test.KEY_NAME + " TEXT,"
				+ "timestamp" + " TEXT )";
		db.execSQL(CREATE_TEST_ACCESS_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_TESTS);
		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_QUESTIONS);
		db.execSQL("DROP TABLE IF EXISTS " + tables.TABLE_ANSWERS);

		// Create tables again
		onCreate(db);
	}

	public boolean deleteTest(Test test) {
		SQLiteDatabase db = this.getWritableDatabase();
		String testId = test.getId();

		List<Question> questions = test.getQuestions();
		if (questions != null) {
			for (Question q : questions) {
				db.delete(tables.TABLE_ANSWERS.toString(), Answer.KEY_QUESTION_ID
						+ "=" + "'" + q.getId() + "'", null);
			}
			db.close();
			close();
		}

		db = getWritableDatabase();
		db.delete(tables.TABLE_QUESTIONS.toString(), Question.KEY_TEST_ID + "="
				+ "'" + testId + "'", null);

		db = getWritableDatabase();
		boolean success = db.delete(tables.TABLE_TESTS.toString(), Test.KEY_ID
				+ "=" + "'" + testId + "'", null) > 0;
		db.close();
		close();
		return success;
	}

	/**
	 * Adds a new test record in the data base, as records of the questions and
	 * answers contained in it
	 * 
	 * @param test
	 */
	public void insertTest(Test test) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues testVal = new ContentValues();
		ContentValues questionVal = new ContentValues();
		ContentValues answerVal = new ContentValues();

		// Insert new test
		String testId = test.getId();
		testVal.put(Test.KEY_ID, testId);
		testVal.put(Test.KEY_NAME, test.getName());
		db.insert(tables.TABLE_TESTS.toString(), null, testVal);

		// Test questions
		List<Question> questions = test.getQuestions();

		if (questions != null) {
			for (Question q : questions) {
				questionVal.put(Question.KEY_TEST_ID, testId);
				questionVal.put(Question.KEY_ID, q.getId());
				questionVal.put(Question.KEY_TEXT, q.getText());
				questionVal.put(Question.KEY_CORRECT_ANSWER, q.getCorrectAnswerId());
				db.insert(tables.TABLE_QUESTIONS.toString(), null, questionVal);

				List<Answer> answers = q.getAnswers();
				String answerId = "";
				for (Answer a : answers) {
					answerId = a.getId();
					answerVal.put(Answer.KEY_ID, answerId);
					answerVal.put(Answer.KEY_QUESTION_ID, q.getId());
					answerVal.put(Answer.KEY_TEXT, a.getText());
					db.insert(tables.TABLE_ANSWERS.toString(), null, answerVal);
				}
			}
		}

		db.close(); // Closing database connection
		close();
	}

	/**
	 * Returns the existing tests in the data base. If getQuestions is true the
	 * returning objects will be fulfilled with the information related to its
	 * questions and answers. If false, it'll only return tests with fields ID
	 * and NAME filled.
	 * 
	 * @param getQuestions
	 * @return
	 */
	public List<Test> getTests(Boolean getQuestions, String whereCLause) {
		List<Test> testsList = new ArrayList<Test>();
		String selectTests = " SELECT * FROM " + tables.TABLE_TESTS.toString();
		
		if (whereCLause != null)
			selectTests += " " + whereCLause;
		
		SQLiteDatabase db = this.getReadableDatabase();

		// SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null,
		// SQLiteDatabase.OPEN_READONLY);
		Cursor c = db.rawQuery(selectTests, null);

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String name = c.getString(1);
				String courseId = c.getString(2);

				if (getQuestions)
					testsList.add(new Test(id, name,
							selectQuestionsFromTest(id), true));
				else
					testsList.add(new Test(id, name, true, courseId));

			} while (c.moveToNext());
		}

		if (getQuestions) {
			for (Test t : testsList)
				t.setQuesions(selectQuestionsFromTest(t.getId()));
		}

		db.close();
		close();
		return testsList;
	}

	/**
	 * 
	 * Returns a list of the questions from a specific test
	 * 
	 * @param idTest
	 * @return
	 */
	private List<Question> selectQuestionsFromTest(String idTest) {
		List<Question> questions = new ArrayList<Question>();
		String selectQuestions = " SELECT * FROM "
				+ tables.TABLE_QUESTIONS.toString() + " WHERE "
				+ Question.KEY_TEST_ID + "=" + "'" + idTest + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null,
				SQLiteDatabase.OPEN_READONLY);
		Cursor c = db.rawQuery(selectQuestions, null);

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String testId = c.getString(1);
				String text = c.getString(2);
				String correctAnswer = c.getString(3);
				questions.add(new Question(id, text, correctAnswer, idTest));

			} while (c.moveToNext());
		}

		for (Question q : questions) {
			q.setAnswers(selectAnswersFromQuestion(db, q.getId()));
		}

		return questions;
	}

	private List<Answer> selectAnswersFromQuestion(SQLiteDatabase db,
			String idQuestion) {
		List<Answer> answers = new ArrayList<Answer>();
		String selectQuestions = " SELECT * FROM "
				+ tables.TABLE_ANSWERS.toString() + " WHERE "
				+ Answer.KEY_QUESTION_ID + "=" + "'" + idQuestion + "'";

		Cursor c = db.rawQuery(selectQuestions, null);

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String text = c.getString(2);
				answers.add(new Answer(id, text, idQuestion));

			} while (c.moveToNext());
		}

		return answers;
	}

	public boolean insertUniversity(University u) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues uniVal = new ContentValues();
		uniVal.put(University.KEY_ID, u.getId());
		uniVal.put(University.NAME, u.getNom());
		uniVal.put(University.ACRONYM, u.getSiglas());
		uniVal.put(University.REGION, u.getRegion());

		return db.insert(tables.TABLE_UNIVERSITIES.toString(), null, uniVal) == -1 ? false
				: true;
	}

	public List<University> getUniversities() {
		SQLiteDatabase db = this.getWritableDatabase();

		List<University> universities = new ArrayList<University>();
		String selectQuestions = " SELECT * FROM "
				+ tables.TABLE_UNIVERSITIES.toString();

		Cursor c = db.rawQuery(selectQuestions, null);

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String name = c.getString(1);
				String acr = c.getString(2);
				String region = c.getString(3);
				universities.add(new University(id, name, acr, region));

			} while (c.moveToNext());
		}

		db.close();
		close();
		return universities;
	}

	public boolean insertDegree(Degree d) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues degVal = new ContentValues();
		degVal.put(Degree.KEY_ID, d.getId());
		degVal.put(Degree.NAME, d.getName());
		degVal.put(Degree.UNI_KEY, d.getIdUni());

		return db.insert(tables.TABLE_DEGREES.toString(), null, degVal) == -1 ? false
				: true;
	}

	public List<Degree> getDegrees() {
		SQLiteDatabase db = this.getWritableDatabase();

		List<Degree> degrees = new ArrayList<Degree>();
		String selectQuestions = " SELECT * FROM "
				+ tables.TABLE_DEGREES.toString();

		Cursor c = db.rawQuery(selectQuestions, null);

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String name = c.getString(1);
				String id_uni = c.getString(2);
				degrees.add(new Degree(id, name, id_uni));

			} while (c.moveToNext());
		}

		db.close();
		close();
		return degrees;
	}

	public boolean existsTest(String testId) {
		String selectTests = " SELECT * FROM " + tables.TABLE_TESTS.toString() + " WHERE "
				+ Test.KEY_ID + "=" + testId;
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor c = db.rawQuery(selectTests, null);
		
		if (c.moveToFirst()) {
			db.close();
			close();
			return true;
		}
		
		db.close();
		close();
		return false;
	}

	public boolean deleteDegree(Degree degree) {
		SQLiteDatabase db = this.getWritableDatabase();
		String id = degree.getId();

		boolean success = db.delete(tables.TABLE_DEGREES.toString(), Degree.KEY_ID
				+ "=" + "'" + id + "'", null) > 0;
				
		db.close();
		close();
		return success;
	}
	
	public boolean insertTestAccess(String testId, String testName) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues val = new ContentValues();
		val.put(Test.KEY_ID, testId);
		val.put(Test.KEY_NAME, testName);
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String formattedDate = sdf.format(date);
		val.put("timestamp", formattedDate);

		if (db.update(tables.TABLE_TEST_ACCESS.toString(), val, Test.KEY_ID + "=" + testId , null) < 1)
			return db.insert(tables.TABLE_TEST_ACCESS.toString(), null, val) == -1 ? false : true;
		
		else return
			true;
	}

	public List<Test> getRecentTests() {
		// TODO Auto-generated method stub
//		A time string can be in any of the following formats:
//
//			YYYY-MM-DD
//			YYYY-MM-DD HH:MM
//			YYYY-MM-DD HH:MM:SS
		//        Cursor mCursor = mDb.query(true, DATABASE_TABLE, null, null, null, null, null, "weekdate ASC", null);
		
		SQLiteDatabase db = this.getWritableDatabase();

		List<Test> tests = new ArrayList<Test>();
//		String selectQuestions = " SELECT * FROM "
//				+ tables.TABLE_TEST_ACCESS.toString();

//		Cursor c = db.rawQuery(selectQuestions, null);
		Cursor c = db.query(true, tables.TABLE_TEST_ACCESS.toString(), null, null, null, null, null, "timestamp DESC", "10");

		if (c.moveToFirst()) {
			do {
				String id = c.getString(0);
				String testName = c.getString(1);
				tests.add(new Test(id, testName, false, null));

			} while (c.moveToNext());
		}

		db.close();
		close();
		return tests;
	}

	public List<Test> getTestsById(List<String> testsIds) {
		List<Test> tests = new ArrayList<Test>();
		String where = "";
		for (String i : testsIds) {
			where = "WHERE " + Test.KEY_ID + "='" + i + "'";
			tests.addAll(getTests(true, where));
		}
		return tests;
	}
}
